<?php

namespace app\services\data\stat\ad\normal;

use app\clickhouse\models\Events;
use app\model\sdk\Orders;

class StatRegRoiMidas extends StatBaseUserEvent
{
    protected $payStime; // 支付开始日期
    protected $payEtime; // 支付截止日期

    public function __construct($params)
    {
        parent::__construct($params);

        $today = strtotime('today');
        if ($this->etime > $today) {
            $this->etime = $today+86400;
        }

        if (isset($params['roi_date'][0]) && isset($params['roi_date'][1])) {
            $this->payStime = $params['roi_date'][0];
            $this->payEtime = $params['roi_date'][1];
        } else {
            $this->payStime = $this->stime;
            $this->payEtime = $today+86400;
        }
    }

    protected function queryGroupBy($table_name = 'users')
    {
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                    $selects[] = "formatDateTime(toDateTime(users.`#create_time`), '%H', '{$this->timezone}') AS stat_hour";
                    break;
                case 'stat_date':
                    $selects[] = "toDate(users.`#create_time`, '{$this->timezone}') AS stat_date";
                    break;
                case 'stat_week':
                    $selects[] = "formatDateTime(toDateTime(users.`#create_time`), '%Y-%V', '{$this->timezone}') AS stat_week";
                    break;
                case 'stat_month':
                    $selects[] = "formatDateTime(toDateTime(users.`#create_time`), '%Y-%m', '{$this->timezone}') AS stat_month";
                    break;
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                    $selects[] = "ad_plan.{$name} AS {$name}";
                    break;
                case 'daili_id':
                    $selects[] = "ad_media_user.{$name} AS {$name}";
                    break;
                case 'adgroup_id':
                    $selects[] = "firstups.adgroup_id AS {$name}";
                    break;
                case 'ad_id':
                    $selects[] = "firstups.ad_id AS {$name}";
                    break;
                case 'creative_id':
                    $selects[] = "firstups.creative_id AS {$name}";
                    break;
                case 'media_user_id':
                    $selects[] = "firstups.{$name} AS {$name}";
                    break;
                case 'game_channel_id':
                    $selects[] = "users.{$name} AS {$name}";
                    break;
                default:
                    $selects[] = "{$name} AS {$name}";
                    break;
            }
        }

        $select_str  = implode(',', $selects);
        $groupby_str = implode(',', $this->groupbys);

        return [$select_str, $groupby_str];
    }

    protected function userSelect()
    {
        $selects = [];
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                case 'stat_date':
                case 'stat_week':
                case 'stat_month':
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                case 'daili_id':
                case 'media_user_id':
                case 'adgroup_id':
                case 'ad_id':
                case 'creative_id':
                    break;
                default:
                    $selects[] = "{$name}";
                    break;
            }
        }

        $select_str = implode(',', $selects);

        return $select_str;
    }

    public function getSqlByGroup()
    {
        $stime   = $this->stime;
        $etime   = $this->etime;
        $paystime = $this->payStime;
        $payetime = $this->payEtime;

        $join             = $this->queryJoin('users');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $event_id         = Events::EVENT_PAY;
        $money_propname   = $this->moneyTypeName;
        $pay_platform     = Orders::PAY_PLATFORM_WECHAT_MIDAS;

        list($select_str, $groupby_str) = $this->queryGroupBy();
        $user_select_str = $this->userSelect();
        if ($user_select_str != '') {
            $user_select_str = ", {$user_select_str}";
        }

        $sql = "
            SELECT {$groupby_str}, cday, count(1) as pay_num, sum(amount) as pay_money, sum(amount2) as pay_money2
            FROM(
                SELECT DISTINCT events.trade_no, toDate(events.`#create_time`, '{$this->timezone}') as pay_day, events.{$money_propname} as amount, (events.{$money_propname}*(10000-cp_fencheng)/10000) as amount2, events.`#user_id`, events.status,
                toDate(users.`#create_time`, '{$this->timezone}') AS cday, {$select_str}
                FROM sdk.events
                JOIN(
                    SELECT `#user_id`, `#create_time`, cp_fencheng, firstups.*, ad_plan.*, ad_media_user.* {$user_select_str} FROM sdk.users_local AS users
                    {$join}
                    WHERE {$user_where}
                    AND `#create_time` >= {$stime}
                    AND `#create_time` < {$etime}
                    AND {$plan_where}
                    AND {$media_user_where}
                ) AS users
                ON
                users.`#user_id` = events.`#user_id`
                WHERE events.`#event_id` = {$event_id}
                AND events.`#create_time` >= {$paystime}
                AND events.`#create_time` < {$payetime}
                AND events.amount > 0
                AND events.is_test = 0
                AND events.pay_platform = {$pay_platform}
            )
            GROUP BY {$groupby_str}, cday
        ";

        return $sql;
    }
}
